
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOI]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************  
* Name:   GetCOI  
* Purpose:  Retrieves the COI with the specified Id  
*  
* PARAMETERS  
* Name         Description       
* -------------      -------------------------------------------  
* @coiId     COI Id  
******************************************************************/  
  
CREATE Procedure [dbo].[GetCOI] (  
  @coiId  INTEGER   
)  
AS  
BEGIN  
  
 SET NOCOUNT ON  
  
 Select COI.Id,  
  AdmPersonId,  
  FwkDomainUserId,  
  LastNotified,  
  LastModified,  
  DateSubmitted,  
  DateReviewed,  
  HasOutsideActivities,  
  HasEquityInterests,  
  HasFamilyAssociations,
  Royalty,  
  OtherActivity,  
  PolicyViolation,  
  AggregateEquity,  
  Status,  
  RefCode.RefMeaning as StatusMeaning, 
  ReportingPeriodId,
  ReportingPeriod,  
  DocumentId,
  COI.COITypeId  
 FROM ConflictOfInterest COI 
	INNER JOIN RefCode ON COI.Status = RefCode.RefCode  
	LEFT JOIN COIReportingPeriods CRP ON COI.ReportingPeriodId = CRP.Id

 WHERE COI.Id = @coiId  
   
END  

GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  

  

  
  